/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Classes.Immo;
import java.beans.Statement;
import java.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.*;

/**
 *
 * @author alexander
 */
public class ImmoDAO implements ImmoDAOInterface {
    
        //final static String dbAddress = "jdbc:mysql://a0752765mysql1.mysql.univie.ac.at:3306/a0752765mysql1";
        //final static String dbUser = "a0752765";    
        //final static String dbUserPassword = "bmjzphcs";
        
        
        final static String dbAddress = "jdbc:mysql://127.0.0.1:3306/immoDB";
        final static String dbUser = "root";    
        final static String dbUserPassword = "geheim";


    
    
    @Override
    public Vector<Immo> readAllImmo() {
        
       String query = "SELECT * FROM IMMO";
            
       Vector<Immo> allImmoVector = new Vector<Immo>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            System.out.println("Treiberklasse nicht gefunden");
            Logger.getLogger(ImmoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
       try {

           Connection con = DriverManager.getConnection(dbAddress, dbUser, dbUserPassword);
           java.sql.Statement stmt = con.createStatement();
           ResultSet rs = stmt.executeQuery(query);
        
           while (rs.next()){
               Immo newImmo = new Immo();
               newImmo.setImmoID(rs.getInt("ImmoID"));
            
               newImmo.setCountry(rs.getString("Country"));
               newImmo.setAddress(rs.getString("Address"));
               newImmo.setImmoCategory(rs.getString("Category"));
               newImmo.setImmoDescription(rs.getString("Description"));
               newImmo.setImmoHeadline(rs.getString("Headline"));
               newImmo.setIsAHouse(rs.getBoolean("IsAHouse"));
               newImmo.setIsSold(rs.getBoolean("IsSold"));
               newImmo.setLinvingArea(rs.getInt("LivingArea"));
               newImmo.setNumberOfRooms(rs.getInt("NumberOfRooms"));
               newImmo.setPrice(rs.getInt("Price"));
               newImmo.setSizeOfTheArea(rs.getInt("SizeOfArea"));
               newImmo.setState(rs.getString("ImmoState"));
               newImmo.setZipCode(rs.getString("ZipCode"));
               newImmo.setSearchString(rs.getString("SearchString"));
               newImmo.setImmoSaleOrRent(Integer.parseInt(rs.getString("SaleOrRent")));
               newImmo.setYear(Integer.parseInt(rs.getString("Jahr")));

               allImmoVector.add(newImmo);
           }
        
        } catch (SQLException ex) {
            Logger.getLogger(ImmoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }    

        
        return allImmoVector;
    }

    @Override
    public Vector<Immo> searchImmo(String search) {
        
       Immo newImmo = new Immo();
        
        Vector<Immo> allSearchImmo = readAllImmo();
        Vector<Immo> searchResult = new Vector<Immo>();
        
        Iterator iter = allSearchImmo.iterator();
        
        while(iter.hasNext()){
            newImmo=(Immo)iter.next();
            if (newImmo.getSearchString().contains(search)) {
                if(!newImmo.isIsSold()){
                    searchResult.add(newImmo);                
                }
            }           
        }

        return searchResult;
    }

    @Override
    public Immo getImmo(String immoID) {
        Immo newImmo = new Immo();
        try {
            String query = "SELECT * FROM IMMO WHERE ImmoID='"+immoID+"'";
            
             try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException ex) {
                System.out.println("Treiberklasse nicht gefunden");
                Logger.getLogger(ImmoDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
          

               Connection con = DriverManager.getConnection(dbAddress, dbUser, dbUserPassword);
               java.sql.Statement stmt = con.createStatement();
               ResultSet rs = stmt.executeQuery(query);
            
               while (rs.next()){
                   newImmo.setImmoID(rs.getInt("ImmoID"));
                   newImmo.setCountry(rs.getString("Country"));
                   newImmo.setAddress(rs.getString("Address"));
                   newImmo.setImmoCategory(rs.getString("Category"));
                   newImmo.setImmoDescription(rs.getString("Description"));
                   newImmo.setImmoHeadline(rs.getString("Headline"));
                   newImmo.setIsAHouse(rs.getBoolean("IsAHouse"));
                   newImmo.setIsSold(rs.getBoolean("IsSold"));
                   newImmo.setLinvingArea(rs.getInt("LivingArea"));
                   newImmo.setNumberOfRooms(rs.getInt("NumberOfRooms"));
                   newImmo.setPrice(rs.getInt("Price"));
                   newImmo.setSizeOfTheArea(rs.getInt("SizeOfArea"));
                   newImmo.setState(rs.getString("ImmoState"));
                   newImmo.setZipCode(rs.getString("ZipCode"));
                   newImmo.setSearchString(rs.getString("SearchString"));
                   newImmo.setImmoSaleOrRent(Integer.parseInt(rs.getString("SaleOrRent")));
                   newImmo.setYear(Integer.parseInt(rs.getString("Jahr")));

                   
               }

        } catch (SQLException ex) {
            Logger.getLogger(ImmoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return newImmo;
    }
    
    @Override
    public boolean saveImmo(Immo immo) {
        
        String  immoCategory = immo.getImmoCategory();
        String  immoHeadLine = immo.getImmoHeadline();
        int     immoLivingArea = immo.getLinvingArea();
        int     immoTotalArea = immo.getSizeOfTheArea();
        int     immoTotalNumberOfRooms = immo.getNumberOfRooms();
        int     immoPrice = immo.getPrice();
        String  immoDetails = immo.getImmoDescription();
        String  immoZipCode = immo.getZipCode();
        String  immoAddresss = immo.getAddress();
        String  immoState = immo.getState();
        String  immoCountry= immo.getCountry();
        boolean isAHouse = immo.isIsAHouse();
        int     immoSaleOrRent = immo.getImmoSaleOrRent();
        
        int isAHouseValue;
        if (isAHouse){
            isAHouseValue=1;
        }
        else{
            isAHouseValue=0;
        }
        
        boolean isSold = immo.isIsSold();
        int isSoldValue;
        
        if(isSold){
            isSoldValue=1;
        }
        else{
            isSoldValue=0;
        }
        
        
        int     immoYear = immo.getYear();
        
        String searchString = immo.getSearchString();
        
        

        
        String query="INSERT INTO IMMO(Country,Address,Category,"+ 
                      "Description,Headline,IsAHouse,IsSold,LivingArea,"+
                      "NumberOfRooms,Price,SizeOfArea,ImmoState,ZipCode,SaleOrRent,SearchString,"+
                      "Jahr) VALUES('"+immoCountry+"','"
                       +immoAddresss+"','"+immoCategory+"','"+immoDetails+"','"+immoHeadLine+"','"+isAHouseValue+"','"
                       +isSoldValue+"','"+immoLivingArea+"','"+immoTotalNumberOfRooms+"','"+immoPrice+"','"
                       +immoTotalArea+"','"+immoState+"','"+immoZipCode+"','"+immoSaleOrRent+"','"+searchString+"','"+immoYear+"')" ;
        
        
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        Connection con;
        try {
            con = DriverManager.getConnection(dbAddress, dbUser, dbUserPassword);
            java.sql.Statement stmt = con.createStatement();
            int rs = stmt.executeUpdate(query);
            
       } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return true;

    }

    @Override
    public boolean updateImmo(String immoID) {
        
        String query = "UPDATE IMMO SET IsSold=true WHERE ImmoID='"+immoID+"'"; 
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            System.out.println("Treiberklasse nicht gefunden");
            Logger.getLogger(ImmoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
        try {
          Connection con = DriverManager.getConnection(dbAddress, dbUser, dbUserPassword);
          java.sql.Statement stmt = con.createStatement();
          int rs = stmt.executeUpdate(query);  
        } catch (SQLException ex) {
            Logger.getLogger(ImmoDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return true;
    }

    @Override
    public Vector<Immo> readAllAvailableImmo() {
        
        Vector<Immo> allImmo = this.readAllImmo();
        Vector<Immo> unsoldImmo = new Vector<Immo>();
        
        
        Iterator iter = allImmo.iterator();
        
        while(iter.hasNext()){
            Immo newImmo = new Immo();
            newImmo = (Immo)iter.next();
            
            if(!newImmo.isIsSold()){
                unsoldImmo.add(newImmo);              
            }
        }
        return unsoldImmo;

    }

    @Override
    public Vector<Immo> readAllSoldImmo() {
    
        Vector<Immo> allImmo = this.readAllImmo();
        Vector<Immo> soldImmo = new Vector<Immo>();
        
        
        Iterator iter = allImmo.iterator();
        
        while(iter.hasNext()){
            Immo newImmo = new Immo();
            newImmo = (Immo)iter.next();
            
            if(newImmo.isIsSold()){
                soldImmo.add(newImmo);              
            }
        }
        return soldImmo;

        
        
        
    }
    
}
